/////// This code is to calculate losses for the SVB extension /////////////////
/// construct mark-to-market loss as of 2024Q1 
/// track performance over time 

global raw "Replication_Package\Data\raw\"
global processed "Replication_Package\Data\processed\"
global output "Replication_Package\Output\"

////////////////////////////////////////////////////////////////////////////////
//////////////////// static mark-to-market losses //////////////////////////////
// find price changes from 2022Q1 to 2024Q1
use "${processed}index_price",clear 
foreach x of var treasury*  rmbs_etf fedfunds {
gen `x'_2022q1 = `x' if date == 248 //2022Q1 
sort `x'_2022q1 
replace `x'_2022q1 = `x'_2022q1[1]
gen d_`x' = `x'/`x'_2022q1
}
format date %tq
sort date 
keep if date==256
keep d_* 
gen quarter =1 
gen rmbs_multiplier = (1-d_rmbs_etf)/(1-d_treasury)

// merge to get 2022Q1 call reports 
merge 1:m quarter using "${raw}callreport_2022q1_clean" 
keep if _merge==3
drop _merge 
replace total_asset = total_asset*1000000 // convert it back into thousands

// treasury 
egen treasury = rowtotal(treasury_htm_amortize treasury_hfs_fair)
egen rmbs = rowtotal(agency_rmbs_htm_amortize  agency_rmbs_hfs_fair other_rmbs_htm_amortize other_rmbs_hfs_fair)
egen cmbs = rowtotal(agency_cmbs_htm_amortize agency_cmbs_hfs_fair other_cmbs_htm_amortize other_cmbs_hfs_fair)
egen abs = rowtotal(abs_htm_amortize abs_hfs_fair)

foreach x of var *_3mo *_1yr *_3yr *_5yr *_15yr *_20yr {
replace `x'=0 if missing(`x')
}
gen loss = (treasury_3mo+treasury_1yr+all_other_loan_3mo+all_other_loan_1yr)*(1-d_treasury_1yr)+ (treasury_3yr+all_other_loan_3yr)*(1-d_treasury_1to3)+(treasury_5yr+all_other_loan_5yr)*(1-d_treasury_3to5)+(treasury_15yr+all_other_loan_15yr)*(1-d_treasury_7to10)+(treasury_20yr+all_other_loan_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) /// non-rmbs securities & non-mortgage loans
+(rmbs_3mo+rmbs_1yr+reloan1to4_3mo+reloan1to4_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (rmbs_3yr+reloan1to4_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(rmbs_5yr+reloan1to4_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(rmbs_15yr+reloan1to4_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(rmbs_20yr+reloan1to4_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier /// rmbs and mortgage 
+(other_rmbs_3yrless)*(1-(d_treasury_1yr+d_treasury_1to3)/2)*rmbs_multiplier + (other_rmbs_3yrmore)*(1-(d_treasury_3to5+d_treasury_7to10+d_treasury_10to20+d_treasury_20plus)/4)*rmbs_multiplier

gen loss_rmbs =(rmbs_3mo+rmbs_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (rmbs_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(rmbs_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(rmbs_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(rmbs_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier +(other_rmbs_3yrless)*(1-(d_treasury_1yr+d_treasury_1to3)/2)*rmbs_multiplier + (other_rmbs_3yrmore)*(1-(d_treasury_3to5+d_treasury_7to10+d_treasury_10to20+d_treasury_20plus)/4)*rmbs_multiplier

gen loss_treasury = (treasury_3mo+treasury_1yr)*(1-d_treasury_1yr)+ treasury_3yr*(1-d_treasury_1to3)+(treasury_5yr)*(1-d_treasury_3to5)+(treasury_15yr)*(1-d_treasury_7to10)+(treasury_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) 
gen loss_reloan = (reloan1to4_3mo+reloan1to4_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (reloan1to4_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(reloan1to4_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(reloan1to4_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(reloan1to4_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier 
gen loss_other_loan = (all_other_loan_3mo+all_other_loan_1yr)*(1-d_treasury_1yr)+ (all_other_loan_3yr)*(1-d_treasury_1to3)+(all_other_loan_5yr)*(1-d_treasury_3to5)+(all_other_loan_15yr)*(1-d_treasury_7to10)+(all_other_loan_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) 

gen r_loss_rmbs = 100*loss_rmbs/loss
gen r_loss_treasury = 100*loss_treasury/loss
gen r_loss_reloan = 100*loss_reloan/loss
gen r_loss_other_loan = 100*loss_other_loan/loss


gen mm_asset = total_asset - loss
gen loss_asset = 100*loss/total_asset
gen uninsured_asset = 100*uninsured_deposit/total_asset
gen uninsured_mm_asset = 100*uninsured_deposit/mm_asset


// create the gsib list 
merge 1:1 idrssd  using "${raw}bank_id"
drop if _merge ==2 
drop _merge
replace namehcr = financialinstitutionname if missing(namehcr)

// this is revised list of gsibs following fed list
replace namehcr = upper(namehcr)
gen gsib = 1 if strpos(namehcr, "ALLY FINANCIAL")>0
replace gsib = 1 if strpos(namehcr, "AMERICAN EXPRESS")>0
replace gsib = 1 if strpos(namehcr, "BANK OF AMERICA")>0
replace gsib = 1 if strpos(namehcr, "MELLON")>0
replace gsib = 1 if strpos(namehcr, "BARCLAYS")>0
replace gsib = 1 if strpos(namehcr, "BMO FINANCIAL")>0
replace gsib = 1 if strpos(namehcr, "BNP PARIBAS")>0
replace gsib = 1 if strpos(namehcr, "CAPITAL ONE")>0
replace gsib = 1 if strpos(namehcr, "CHARLES SCHWAB")>0
replace gsib = 1 if strpos(namehcr, "CITIGROUP")>0
replace gsib = 1 if strpos(namehcr, "CITIZENS FINANCIAL GROUP")>0 & rssdhcr==1132449
replace gsib = 1 if rssdhcr== 1574834 //strpos(namehcr, "CREDIT SUISSE")>0
replace gsib = 1 if rssdhcr==2816906 // strpos(namehcr, "DB USA")>0
replace gsib = 1 if strpos(namehcr, "DISCOVER")>0
replace gsib = 1 if strpos(namehcr, "FIFTH THIRD")>0
replace gsib = 1 if strpos(namehcr, "GOLDMAN SACHS")>0 & rssdhcr==2380443
replace gsib = 1 if strpos(namehcr, "HSBC")>0
replace gsib = 1 if strpos(namehcr, "HUNTINGTON")>0 & rssdhcr ==  1068191
replace gsib = 1 if strpos(namehcr, "JPMORGAN")>0
replace gsib = 1 if strpos(namehcr, "KEYCORP")>0
replace gsib = 1 if strpos(namehcr, "M&T BANK")>0
replace gsib = 1 if strpos(namehcr, "MORGAN STANLEY")>0
replace gsib = 1 if rssdhcr == 1378434 //strpos(namehcr, "MUFG")>0  
replace gsib = 1 if strpos(namehcr, "NORTHERN TRUST")>0
replace gsib = 1 if rssdhcr == 1069778 //strpos(namehcr, "PNC FINANCIAL")>0
replace gsib = 1 if strpos(namehcr, "RBC")>0
replace gsib = 1 if strpos(namehcr, "REGIONS FINANCIAL")>0
replace gsib = 1 if strpos(namehcr, "SANTANDER")>0
replace gsib = 1 if rssdhcr == 1111435 //strpos(namehcr, "STATE STREET")>0
replace gsib = 1 if rssdhcr == 3606542 //strpos(namehcr, "TD GROUP")>0
replace gsib = 1 if strpos(namehcr, "TRUIST FINANCIAL")>0
replace gsib = 1 if strpos(namehcr, "UBS")>0
replace gsib = 1 if rssdhcr == 1119794 //strpos(namehcr, "US BANCORP")>0
replace gsib = 1 if rssdhcr == 1120754 // strpos(namehcr, "WELLS FARGO")>0


replace size_bin = "gsib" if gsib==1
replace size_bin = "large" if gsib==0 & size_bin=="gsib"

gen small = (size_bin=="small")
gen large = (size_bin == "large") 
save "${processed}analysis_loss_2022q1_2024q1",replace 


////////////////////////////////////////////////////////////////////////////////
//////// sample construction for the extend-and-pretend analysis //////////////
////////////////////////////////////////////////////////////////////////////////
use "${processed}index_price",clear 
foreach x of var treasury*  rmbs_etf fedfunds {
gen `x'_2022q1 = `x' if date == 248 //2022Q1 
sort `x'_2022q1 
replace `x'_2022q1 = `x'_2022q1[1]
gen d_`x' = `x'/`x'_2022q1
}
format date %tq
sort date 
keep if date==256
keep d_* 
gen quarter =1 
gen rmbs_multiplier = (1-d_rmbs_etf)/(1-d_treasury)

// merge to get 2022Q1 call reports 
merge 1:m quarter using "${raw}callreport_2022q1_clean" 
keep if _merge==3
drop _merge 
replace total_asset = total_asset*1000000 // convert it back into thousands

// treasury 
egen treasury = rowtotal(treasury_htm_amortize treasury_hfs_fair)
egen rmbs = rowtotal(agency_rmbs_htm_amortize  agency_rmbs_hfs_fair other_rmbs_htm_amortize other_rmbs_hfs_fair)
egen cmbs = rowtotal(agency_cmbs_htm_amortize agency_cmbs_hfs_fair other_cmbs_htm_amortize other_cmbs_hfs_fair)
egen abs = rowtotal(abs_htm_amortize abs_hfs_fair)

foreach x of var *_3mo *_1yr *_3yr *_5yr *_15yr *_20yr {
replace `x'=0 if missing(`x')
}
gen loss = (treasury_3mo+treasury_1yr+all_other_loan_3mo+all_other_loan_1yr)*(1-d_treasury_1yr)+ (treasury_3yr+all_other_loan_3yr)*(1-d_treasury_1to3)+(treasury_5yr+all_other_loan_5yr)*(1-d_treasury_3to5)+(treasury_15yr+all_other_loan_15yr)*(1-d_treasury_7to10)+(treasury_20yr+all_other_loan_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) /// non-rmbs securities & non-mortgage loans
+(rmbs_3mo+rmbs_1yr+reloan1to4_3mo+reloan1to4_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (rmbs_3yr+reloan1to4_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(rmbs_5yr+reloan1to4_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(rmbs_15yr+reloan1to4_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(rmbs_20yr+reloan1to4_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier /// rmbs and mortgage 
+(other_rmbs_3yrless)*(1-(d_treasury_1yr+d_treasury_1to3)/2)*rmbs_multiplier + (other_rmbs_3yrmore)*(1-(d_treasury_3to5+d_treasury_7to10+d_treasury_10to20+d_treasury_20plus)/4)*rmbs_multiplier

gen loss_rmbs =(rmbs_3mo+rmbs_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (rmbs_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(rmbs_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(rmbs_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(rmbs_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier +(other_rmbs_3yrless)*(1-(d_treasury_1yr+d_treasury_1to3)/2)*rmbs_multiplier + (other_rmbs_3yrmore)*(1-(d_treasury_3to5+d_treasury_7to10+d_treasury_10to20+d_treasury_20plus)/4)*rmbs_multiplier

gen loss_treasury = (treasury_3mo+treasury_1yr)*(1-d_treasury_1yr)+ treasury_3yr*(1-d_treasury_1to3)+(treasury_5yr)*(1-d_treasury_3to5)+(treasury_15yr)*(1-d_treasury_7to10)+(treasury_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) 
gen loss_reloan = (reloan1to4_3mo+reloan1to4_1yr)*(1-d_treasury_1yr)*rmbs_multiplier+ (reloan1to4_3yr)*(1-d_treasury_1to3)*rmbs_multiplier+(reloan1to4_5yr)*(1-d_treasury_3to5)*rmbs_multiplier+(reloan1to4_15yr)*(1-d_treasury_7to10)*rmbs_multiplier+(reloan1to4_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2)*rmbs_multiplier 
gen loss_other_loan = (all_other_loan_3mo+all_other_loan_1yr)*(1-d_treasury_1yr)+ (all_other_loan_3yr)*(1-d_treasury_1to3)+(all_other_loan_5yr)*(1-d_treasury_3to5)+(all_other_loan_15yr)*(1-d_treasury_7to10)+(all_other_loan_20yr)*(1-(d_treasury_10to20+d_treasury_20plus)/2) 

gen r_loss_rmbs = 100*loss_rmbs/loss
gen r_loss_treasury = 100*loss_treasury/loss
gen r_loss_reloan = 100*loss_reloan/loss
gen r_loss_other_loan = 100*loss_other_loan/loss


gen mm_asset = total_asset - loss
gen loss_asset = 100*loss/total_asset
gen uninsured_asset = 100*uninsured_deposit/total_asset
gen uninsured_mm_asset = 100*uninsured_deposit/mm_asset

///////////////////////// combine with the full call report up to 2024Q1 ///////
merge 1:1 idrssd using "${raw}bank_id"
keep if _merge==3
drop _merge 

drop quarter
// capital ratio measure 
gen capital_ratio_50 = (total_equity-0.5*uninsured_deposit*(total_asset/(total_asset-loss)-1))/total_asset 
gen capital_ratio_100 = (total_equity-uninsured_deposit*(total_asset/(total_asset-loss)-1))/total_asset 

// uninured depositor run ratio 
gen insured_coverage_mm_50 = 100*(total_asset - loss - 0.5*uninsured_deposit-insured_deposit)/insured_deposit // use 50% to define endangered banks
gen endangered_50 = (insured_coverage_mm_50<0)

gen insured_coverage_mm_100 = 100*(total_asset - loss - uninsured_deposit-insured_deposit)/insured_deposit 
gen endangered_100 = (insured_coverage_mm_100<0)

gen mm_equity = total_equity - loss
gen mm_r_total_equity = 100*mm_equity/mm_asset 
gen endangered_equity = (mm_r_total_equity<0)

gen charter_group = 1 if charter == "FED"
replace charter_group = 2 if charter=="STATE" & regagnt=="FED"
replace charter_group = 3 if charter=="STATE" & regagnt=="FDIC"

foreach x of var capital_ratio* insured_coverage_mm* mm_r_total_equity{
	egen bin_`x' = xtile(`x'), nq(4) 
}
keep idrssd capital_ratio* insured_coverage_mm* endangered* mm_r_total_equity bin_*  charter charter_group regagnt
merge 1:m idrssd using "${raw}callreport_2124_clean"
keep if _merge ==3
drop _merge 

// find duration 
egen total_asset_for_duration = rowtotal(treasury_3mo treasury_1yr treasury_3yr treasury_5yr treasury_15yr treasury_20yr ///
all_other_loan_3mo all_other_loan_1yr all_other_loan_3yr all_other_loan_5yr all_other_loan_15yr all_other_loan_20yr ///
other_rmbs_3yrless other_rmbs_3yrmore rmbs_3mo rmbs_1yr rmbs_3yr rmbs_5yr rmbs_15yr rmbs_20yr ///
reloan1to4_3mo reloan1to4_1yr reloan1to4_3yr reloan1to4_5yr reloan1to4_15yr reloan1to4_20yr)

gen duration = ((treasury_3mo+all_other_loan_3mo+reloan1to4_3mo+rmbs_3mo)/total_asset_for_duration)*(3/12)/2 ///
+ ((treasury_1yr+all_other_loan_1yr+rmbs_1yr+reloan1to4_1yr)/total_asset_for_duration)*(3/12+1)/2 ///
+ ((treasury_3yr+all_other_loan_3yr+rmbs_3yr+reloan1to4_3yr)/total_asset_for_duration)*(1+3)/2 ///
+((treasury_5yr+all_other_loan_5yr+rmbs_5yr+reloan1to4_5yr)/total_asset_for_duration)*(3+5)/2 ///
+((treasury_15yr+all_other_loan_15yr+rmbs_15yr+reloan1to4_15yr)/total_asset_for_duration)*(5+15)/2 ///
+((treasury_20yr+all_other_loan_20yr+rmbs_20yr+reloan1to4_20yr)/total_asset_for_duration)*20 ///
+(other_rmbs_3yrless/total_asset_for_duration)*1.5+(other_rmbs_3yrmore/total_asset_for_duration)*5

gen share_long_term = (treasury_15yr+treasury_20yr+all_other_loan_15yr+all_other_loan_20yr+rmbs_15yr+rmbs_20yr+reloan1to4_15yr+reloan1to4_20yr)/total_asset_for_duration
gen share_short_term = (treasury_3mo+treasury_1yr+all_other_loan_3mo+ all_other_loan_1yr+rmbs_3mo+rmbs_1yr+reloan1to4_3mo+reloan1to4_1yr)/total_asset_for_duration


save "${processed}analysis_loss_dynamic_report_2021q12024q1",replace 



gen cre_loan = reloan-reloan_residential1to4 
gen cre_at = cre_loan/(total_asset*1000000)
gen cre_to_loan= cre_loan/(total_loan)

gen date = ym(year,month)
gen quarter  = quarter(dofq(date))
gen date_q = yq(year,quarter)
drop date 
ren date_q date
xtset idrssd date 


// CRE is defined as non 1-4 family residential properties 
egen nonperform_total = rowtotal(past_due_30_89_total past_due_90_total past_due_non_accrual_total)
egen nonperform_cre = rowtotal(past_due_30_89_cre past_due_90_cre past_due_nonaccrual_cre)

gen s_nonperform_total = 100*nonperform_total/total_loan 
gen s_nonperform_cre = 100*nonperform_cre/cre_loan 

gen post = (date>=248)


gen ln_asset = ln(total_asset*1000000)
egen ave_ln_asset = mean(ln_asset), by(idrssd)
save "${processed}analysis_cre_dynamic_state",replace


